import string
from sqlite3 import IntegrityError
from typing import List, Optional

from sqlalchemy.orm import Session, joinedload
from .database import SessionLocal
from .models import Video, Tag, video_tag_association
from pathlib import Path
import os
from datetime import datetime


def add_tag(name: str) -> Tag:
    """添加新标签"""
    db = SessionLocal()
    try:
        tag = Tag(name=name)
        db.add(tag)
        db.commit()
        db.refresh(tag)
        return tag
    except IntegrityError:
        db.rollback()
        raise ValueError(f"标签 '{name}' 已存在")
    finally:
        db.close()


def delete_tag(tag_id: int) -> bool:
    """删除标签"""
    db = SessionLocal()
    try:
        tag = db.query(Tag).filter(Tag.id == tag_id).first()
        if not tag:
            return False

        # 检查是否有视频使用此标签
        if tag.videos:
            raise ValueError("该标签正在被使用，无法删除")

        db.delete(tag)
        db.commit()
        return True
    except Exception as e:
        db.rollback()
        raise e
    finally:
        db.close()


def update_tag(tag_id: int, new_name: str) -> Tag:
    """更新标签名称"""
    db = SessionLocal()
    try:
        tag = db.query(Tag).filter(Tag.id == tag_id).first()
        if not tag:
            raise ValueError("标签不存在")

        tag.name = new_name
        db.commit()
        db.refresh(tag)
        return tag
    except IntegrityError:
        db.rollback()
        raise ValueError(f"标签 '{new_name}' 已存在")
    except Exception as e:
        db.rollback()
        raise e
    finally:
        db.close()
def search_videos_advanced(
    db: Session,
    name: Optional[str] = None,
    tag_name: Optional[str] = None,
    score: Optional[float] = None,
    untagged_only: bool = False,
    unrated_only: bool = False
) -> List[Video]:
    """高级视频搜索（修复会话问题）"""
    # 使用 joinedload 预加载 tags 关系

    query = db.query(Video).options(joinedload(Video.tags))

    # 名称模糊搜索
    if name:
        query = query.filter(Video.name.ilike(f"%{name}%"))

    # 标签搜索
    if tag_name:
        query = query.join(Video.tags).filter(Tag.name == tag_name)
    elif untagged_only:
        query = query.filter(~Video.tags.any())

    # 评分搜索
    if score is not None:
        query = query.filter(Video.score == score)
    elif unrated_only:
        query = query.filter(Video.score == None)

    # 执行查询并返回结果（注意：必须在会话关闭前处理所有关系数据）
    return query.distinct().all()
def get_all_tags() -> List[Tag]:
    db = SessionLocal()
    """
    获取数据库中的所有标签
    返回: 标签对象列表
    """
    return db.query(Tag).order_by(Tag.name).all()

def get_all_videos() -> list[Video]:
    """获取所有视频（带标签预加载）"""
    db = SessionLocal()
    try:
        return db.query(Video).options(joinedload(Video.tags)).all()
    finally:
        db.close()


def get_video_by_id(video_id: int) -> Video:
    """根据ID获取单个视频"""
    db = SessionLocal()
    try:
        return db.query(Video).filter(Video.id == video_id).first()
    finally:
        db.close()



def rate_video(video_id: int, score: float):
    """更新视频评分"""
    db = SessionLocal()
    try:
        video = db.query(Video).get(video_id)
        if video:
            video.score = score
            db.commit()
    finally:
        db.close()


def add_tag_to_video(video_id: int, tag_name: str):
    """添加标签到视频"""
    db = SessionLocal()
    try:
        # 查找或创建标签
        tag = db.query(Tag).filter(Tag.name == tag_name).first()
        if not tag:
            tag = Tag(name=tag_name)
            db.add(tag)
            db.flush()

        # 建立关联
        video = db.query(Video).get(video_id)
        if video and tag:
            if tag not in video.tags:
                video.tags.append(tag)
                db.commit()
    finally:
        db.close()


def search_videos(keyword: str) -> list[Video]:
    """搜索视频"""
    db = SessionLocal()
    try:
        return db.query(Video).filter(
            Video.name.ilike(f"%{keyword}%")
        ).options(joinedload(Video.tags)).all()
    finally:
        db.close()


def import_videos_from_folder(folder_path: str) -> int:
    """导入文件夹中的视频到数据库（返回导入数量）"""
    db = SessionLocal()
    try:
        count = 0
        for root, _, files in os.walk(Path(folder_path)):
            for file in files:
                if file.lower().endswith(('.mp4', '.ts', '.avi', '.mov', '.mkv')):
                    video_path = Path(root) / file

                    # 跳过已存在的视频（根据路径判断更准确）
                    if not db.query(Video).filter(Video.url == str(video_path)).first():
                        db_video = Video(
                            name=file,
                            url=str(video_path),
                            create_time=datetime.now()
                        )
                        db.add(db_video)
                        count += 1

        db.commit()
        return count
    except Exception as e:
        db.rollback()
        raise ValueError(f"导入失败: {str(e)}")
    finally:
        db.close()